package com.rongji.cms.tools.temp.db.utils;


import com.rongji.cms.tools.temp.db.entity.OracleColumnProp;

import java.sql.*;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;


/**
 * @author yang
 * @create 2023/1/12
 * 改成oracle的
 **/
public class ORACLEUtil {
    public final static String ORACLE_DRIVER = "oracle.jdbc.driver.OracleDriver";
    public final static String GET_TABLE_NAME = " select table_name  from tabs  ";

    /**
     * 功能描述: <br>
     * 〈〉
     *
     * @Param: [url, user, pwd]
     * @Return: java.sql.Connection
     * @Author: luoxw
     * @Date: 2021/6/4 18:25
     */
    public static Connection getORACLEConnection(String url, String user, String pwd) {
        Connection conn = null;
        try {
            Class.forName(ORACLE_DRIVER).newInstance();
            conn = DriverManager.getConnection(url, user, pwd);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 功能描述: <br>
     * 〈〉
     *
     * @Param: [host, dbName]
     * @Return: java.lang.String
     * @Author: luoxw
     * @Date: 2021/6/4 18:25
     */
    public static String getConnectUrl(String host, String dbName) {
        StringBuilder sb = new StringBuilder("jdbc:oracle:thin:@");
        sb.append(host).append(":").append(dbName);
        return sb.toString();
    }

    /**
     * 功能描述: <br>
     * 〈〉
     *
     * @Param: [dbName, tableName]
     * @Return: java.lang.String
     * @Author: luoxw
     * @Date: 2021/6/4 18:25
     */
    public static String getColumnsSQL(String tableName) {
//        select * from user_tab_cols where table_name='BACK_LOG';
//        user_tab_columns更合适
        StringBuilder sb = new StringBuilder("select * from user_tab_columns  ");
        sb.append(" where TABLE_NAME = '").append(tableName).append("'");
        return sb.toString();
    }

    /**
     * 功能描述: <br>
     * 〈〉
     *
     * @Param: [host, dbName, userName, userPwd]
     * @Return:
     * @Author: luoxw
     * @Date: 2021/6/4 18:25
     */
    public static Map<String, Map<String, OracleColumnProp>> getDBInfo(String host, String dbName, String userName, String userPwd) throws Exception {
        Map<String, Map<String, OracleColumnProp>> tableMap = new ConcurrentHashMap<String, Map<String, OracleColumnProp>>();
        Connection conn = null;
        Statement tableStatement = null;
        ResultSet tableResultSet = null;
        try {
            conn = getORACLEConnection(getConnectUrl(host, dbName), userName, userPwd);
            tableStatement = conn.createStatement();
//          oracle获取表名得这样
            tableResultSet = tableStatement.executeQuery(GET_TABLE_NAME);
            int tableColumnCount = tableResultSet.getMetaData().getColumnCount();
            while (tableResultSet.next()) {
                for (int i = 1; i <= tableColumnCount; i++) {
                    String tableName = tableResultSet.getString(i);
                    System.out.println("------------比较表:" + tableName);
                    ResultSet columnResultSet = null;
                    Statement columnStatement = null;
                    try {
                        columnStatement = conn.createStatement();
                        String columnSql = getColumnsSQL(tableName);
                        columnResultSet = columnStatement.executeQuery(columnSql);
                        Map<String, OracleColumnProp> columnsMap = OracleColumnProp.getFromResult(columnResultSet);
                        tableMap.put(tableName, columnsMap);
                    } finally {
                        closeConnection(columnResultSet, columnStatement, null);
                    }
                }
            }
        } finally {
            closeConnection(tableResultSet, tableStatement, conn);

        }
        return tableMap;
    }


    /**
     * 功能描述: <br>
     * 〈〉
     *
     * @Param: [rSet, psment, conn]
     * @Return: void
     * @Author: luoxw
     * @Date: 2021/6/4 18:29
     */
    public static void closeConnection(ResultSet rSet, Statement psment, Connection conn) throws Exception {
        try {
            if (rSet != null) {
                rSet.close();
                rSet = null;
            }
            if (psment != null) {
                psment.close();
                psment = null;
            }
            if (conn != null && !conn.isClosed()) {
                conn.close();
                conn = null;
            }
        } catch (SQLException e) {
            throw new Exception("Fail to close ResultSet / PreParedStatment / Connection !");
        }
    }
}
